﻿#include "widget.h"
#include "ui_widget.h"
#include <QtDebug>
#include <QMessageBox>
#include <QSqlRecord>
#include <QDir>
#include <QFileDialog>
#include <QTextCodec>


Widget::Widget(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::Widget)
{
    ui->setupUi(this);

    connect( ui->selectBtn, &QPushButton::clicked, this, &Widget::db_select );
    connect( ui->tableWidget, &QTableWidget::cellClicked, this, &Widget::fill_edit );
    connect( ui->exportBtn, &QPushButton::clicked, this, &Widget::export_data );


    ui->tableWidget->setColumnCount(4);
    ui->tableWidget->setHorizontalHeaderLabels(QStringList()<<"Number"<<"Chinese"<<"English"<<"WordClass");
    ui->tableWidget->setColumnWidth( 0, 50 );
    ui->tableWidget->setColumnWidth( 3, 100 );
    ui->tableWidget->horizontalHeader()->setSectionResizeMode( 1, QHeaderView::Stretch ) ;
    ui->tableWidget->horizontalHeader()->setSectionResizeMode( 2, QHeaderView::Stretch ) ;
    ui->tableWidget->verticalHeader()->hide();
    ui->tableWidget->horizontalHeader()->setFont( QFont( "黑体" ) );
    ui->tableWidget->setAlternatingRowColors(true);
    ui->tableWidget->horizontalHeader()->setDefaultAlignment( Qt::AlignHCenter|Qt::AlignVCenter );
    ui->tableWidget->setSelectionBehavior( QAbstractItemView::SelectRows );
    ui->tableWidget->setSelectionMode( QAbstractItemView::SingleSelection );
    ui->tableWidget->setEditTriggers(QAbstractItemView::NoEditTriggers);

    QDir path;
    if ( path.exists("Database") == false )
    {
        path.mkdir("Database");
    }
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(".//Database//dictionary_ll.db");
    if ( !db.open() )
    {
        qDebug()<<"open err"<<db.lastError();
        qDebug()<<"open err"<<db.lastError().text();
        qDebug()<<QMessageBox::critical( this, "错误", "没有找到数据库！" );
    }
    QSqlQuery query;
    if ( db.tables().contains("dictionary") == true )
    {
        qDebug()<<"表已经存在";
        /* 兼容老表 */
        QString sqlSelectTable = QString( "select * from dictionary;" );
        if ( !query.exec( sqlSelectTable ) )
        {
            qDebug()<<sqlSelectTable;
            qDebug()<<"sqlSelectTable"<<db.lastError();
        }
        if ( query.record().contains("WordClass") == false )
        {
            /* add new colomn */
            QString sqlAddColumnTable = QString( "alter table dictionary add column WordClass TEXT;" );
            if ( !query.exec( sqlAddColumnTable ) )
            {
                qDebug()<<sqlAddColumnTable;
                qDebug()<<"sqlAddColumnTable"<<db.lastError();
                qDebug()<<"sqlAddColumnTable"<<db.lastError().text();
            }
        }
        else
        {
            qDebug()<<"新列已经添加";
        }
    }
    else
    {
        QString sqlCreateTable = QString( "create table dictionary(id integer primary key autoincrement,"
                                  "Chinese TEXT,"
                                  "English TEXT,"
                                  "WordClass TEXT);");
        if ( !query.exec( sqlCreateTable ) )
        {
            qDebug()<<sqlCreateTable;
            qDebug()<<"create table"<<db.lastError();
        }
    }
    db_select();
}

Widget::~Widget()
{
    if ( db.isOpen() )
    {
        qDebug()<<"db.close";
        db.close();
    }
    delete ui;
}

void Widget::db_select()
{
    ui->tableWidget->setRowCount(0);
    index.clear();
    QSqlQuery query;
    QString sqlSelect = QString( "select * from dictionary;" );
    if ( !query.exec( sqlSelect ) )
    {
        qDebug()<<"sqlSelect db err"<<db.lastError();
    }
    uint32_t row=0,nCount;
    while ( query.next() )
    {
        int id = query.value("id").toInt();
        QString Chinese = query.value("Chinese").toString();
        QString English = query.value("English").toString();
        QString WordClass = query.value("WordClass").toString();
        index.append( id );
//        qDebug()<<row<<" "<<Chinese<<" "<<English<<" "<<index[row];
        /* 添加到UI */
        nCount = ui->tableWidget->rowCount();
        ui->tableWidget->insertRow( nCount );
        ui->tableWidget->setItem( row, 0, new QTableWidgetItem( QString::number(row+1) ));
        ui->tableWidget->setItem( row, 1, new QTableWidgetItem( Chinese ) );
        ui->tableWidget->setItem( row, 2, new QTableWidgetItem( English ) );
        ui->tableWidget->setItem( row, 3, new QTableWidgetItem( WordClass ) );
        row++;
    }
    ui->idlineEdit->setText("");
    ui->ChinesetextEdit->setText("");
    ui->EnglishtextEdit->setText("");
    ui->WordClassEdit->setText("");
}


void Widget::on_deleteBtn_clicked()
{
    QString str;
    QList<QTableWidgetItem*>items = ui->tableWidget->selectedItems();

    int nCount = items.count();
    if ( nCount > 0 )
    {
        qDebug()<<"Row="<<items.at(0)->text()<<" "
                <<"Chinese="<<items.at(1)->text()<<" "
                <<"English="<<items.at(2)->text();
        int nCurrRow  = ui->tableWidget->currentRow();
        str = "确认删除:\r\n";
        str +=items.at(0)->text();
        str += " ";
        str +=items.at(1)->text();
        str += " ";
        str +=items.at(2)->text();
        if ( !QMessageBox::warning( this, "warning", str, "OK", "cannel" ))
        {
            QSqlQuery query;
            QString sqlDelete = QString( "delete from dictionary where id=%1 AND Chinese='%2' AND English='%3';" )
                                .arg( QString::number(index[ nCurrRow ]) , items.at(1)->text() , items.at(2)->text() );
            if ( !query.exec( sqlDelete ) )
            {
                qDebug()<<sqlDelete;
                qDebug()<<"sqlDelete db err"<<db.lastError();
            }
        }
        db_select();
        if ( curSelect >=( ui->tableWidget->rowCount()-1) )
        {
            curSelect = ui->tableWidget->rowCount()-1;
        }
        ui->tableWidget->selectRow( curSelect );
    }
}


void Widget::on_addBtn_clicked()
{   
    int id = ui->idlineEdit->text().toInt();
    QString Chinese = ui->ChinesetextEdit->toPlainText();
    QString English = ui->EnglishtextEdit->toPlainText();
    QString WordClass = ui->WordClassEdit->toPlainText();
    qDebug()<<id<<" "<<Chinese<<" "<<English;

    if (( Chinese.count() == 0 )||( English.count() == 0 ))
    {
        QMessageBox::critical(this,"错误", "请补全参数重新输入" );
        qDebug()<<"参数不全";
        return ;
    }
    QString str;
    str = "确认添加?\r\n";
    str +=Chinese;
    str += " ";
    str +=English;
    str += " ";
    str +=WordClass;
    if ( !QMessageBox::warning( this, "warning", str, "OK", "cannel" ))
    {
        QSqlQuery query;
        QString sqlInsert = QString("insert into dictionary(Chinese,English,WordClass)"
                                    "values('%1','%2','%3');").arg( Chinese, English, WordClass );
        if ( !query.exec( sqlInsert ) )
        {
            qDebug()<<sqlInsert;
            qDebug()<<"insert into data err"<<db.lastError();
        }
        db_select();
        ui->tableWidget->selectRow( ui->tableWidget->rowCount()-1 );
    }
}

void Widget::on_updataBtn_clicked()
{
    QString id = ui->idlineEdit->text();
    QString Chinese = ui->ChinesetextEdit->toPlainText();
    QString English = ui->EnglishtextEdit->toPlainText();
    QString WordClass = ui->WordClassEdit->toPlainText() ;
    qDebug()<<id<<" "<<Chinese<<" "<<English;
    if (id.isEmpty()||Chinese.isEmpty()|| English.isEmpty())
    {
        qDebug()<<"请输入参数";
        QMessageBox::critical(this,"错误", "请补全参数重新输入" );
        return;
    }
    int nCurrRow  = ui->tableWidget->currentRow();
    QString str;
    str = "确认更改:\r\n";
    str += id ;
    str += " ";
    str +=Chinese;
    str += " ";
    str +=English;
    str += " ";
    str +=WordClass;
    if ( !QMessageBox::warning( this, "warning", str, "OK", "cannel" ))
    {
        QSqlQuery query;
        QString sqlupdate = QString("update dictionary set Chinese = '%1', English = '%2', WordClass = '%3' where id = %4;")
                            .arg( Chinese, English, WordClass, QString::number( index[nCurrRow]) );

        if ( !query.exec( sqlupdate ) )
        {
            qDebug()<<sqlupdate;
            qDebug()<<"insert into data err"<<db.lastError();
        }
        db_select();
        ui->tableWidget->selectRow( curSelect );
    }
}

void Widget::export_data()
{
    QString filePath = QFileDialog::getSaveFileName( this, "Save File", QString(), "csv(*.csv);" );
    if ( filePath.length() > 0 )
    {
        qDebug()<<"patch:"<<filePath;
        QFile file( filePath );
        if ( file.open(QIODevice::WriteOnly) == true )
        {
            QSqlQuery query;
            QString sqlSelect = QString( "select * from dictionary;" );
            if ( !query.exec( sqlSelect ) )
            {
                qDebug()<<"sqlSelect db err"<<db.lastError();
            }
            uint32_t nCount = 1;
            QString Chinese ;
            QString English ;
            QString WordClass ;
            QString string;
            QTextStream stream( &file );
            stream<<"Number,Chinese,English,WordClass\r\n";
            while ( query.next() )
            {
                Chinese = query.value("Chinese").toString();
                English = query.value("English").toString();
                WordClass = query.value("WordClass").toString();
                Chinese.replace( "\"", "\"\"" );
                English.replace( "\"", "\"\"" );
                WordClass.replace( "\"", "\"\"" );
                string = "\"" + QString::number( nCount ) +  "\"" + ",";
                string += "\"" + Chinese +  "\"" + ",";
                string += "\"" + English +  "\"" + ",";
                string += "\"" + WordClass +  "\"";
                string += "\r\n";
                stream<<string;
                nCount++;
            }
            file.close();
            QMessageBox::information( this, "温馨提示:", "数据库导出完成" );
        }
    }
}

void Widget::fill_edit(int row, int column)
{
    qDebug()<<"row="<<row<<" column="<<column;
    QList<QTableWidgetItem*>items = ui->tableWidget->selectedItems();
    curSelect = row;
    if ( items.count() > 0 )
    {
        ui->idlineEdit->setText( items.at(0)->text() );
        ui->ChinesetextEdit->setText( items.at(1)->text() );
        ui->EnglishtextEdit->setText( items.at(2)->text() );
        ui->WordClassEdit->setText( items.at(3)->text() );
    }
    else
    {
        ui->idlineEdit->setText("");
        ui->ChinesetextEdit->setText("");
        ui->EnglishtextEdit->setText("");
        ui->EnglishtextEdit->setText("");
    }
}


